package com.pzhu.record.constants;

import com.pzhu.record.entity.Achievement;
import com.pzhu.record.entity.Basicinformation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;

/**
 * 导出Excel
 *
 * @author Zhou YuSen
 * @date 2020/3/30 20:45
 */
public class ExcelWriter<T> {
    //列头
    private static List<String> CELL_HEADS;

    /**
     * 生成sheet表，并写入第一行数据（列头）
     *
     * @param workbook workbook 工作簿对象
     * @return 已经写入列头的Sheet
     */
    private static Sheet buildDataSheet(Workbook workbook) {
        //创建工作表
        Sheet sheet = workbook.createSheet();
        // 设置列头宽度
        for (int i = 0; i < CELL_HEADS.size(); i++) {
            sheet.setColumnWidth(i, 4000);
        }
        //设置行高
        sheet.setDefaultRowHeight((short) 400);
        // 构建头单元格样式
        CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());
        //写入第一行各列的数据
        Row head = sheet.createRow(0);
        for (int i = 0; i < CELL_HEADS.size(); i++) {
            Cell cell = head.createCell(i);
            cell.setCellValue(CELL_HEADS.get(i));
            cell.setCellStyle(cellStyle);
        }
        return sheet;
    }

    /**
     * 设置第一行列头的样式
     *
     * @param workbook 工作簿对象
     * @return 单元格样式对象
     */
    private static CellStyle buildHeadCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //设置对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        //边框颜色和宽度设置
        //下划线
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        //设置背景颜色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //粗字体设置
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    /**
     * 设置单元格的样式
     *
     * @param workbook 工作簿对象
     * @return 单元格样式对象
     */
    private static CellStyle buildCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //设置对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        //边框颜色和宽度设置
        //下划线
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }

    /**
     * 学生信息表头
     * 类装载时就载入指定好的列头信息，如有需要，可以考虑做成动态生成的列头
     *
     * @return 列表头集合
     */
    private static List<String> addStuList() {
        List<String> excelList = new ArrayList<>();
        excelList.add("学号");
        excelList.add("姓名");
        excelList.add("院系");
        excelList.add("专业班级");
        excelList.add("身份证号");
        excelList.add("家庭住址");
        excelList.add("获取性别");
        excelList.add("民族");
        excelList.add("联系电话");
        excelList.add("出生地");
        excelList.add("籍贯");
        excelList.add("生源地");
        excelList.add("考生号");
        excelList.add("角色");
        excelList.add("密码");
        excelList.add("贷款金额");
        excelList.add("贷款回执单");
        excelList.add("贷款类型");
        return excelList;
    }

    /**
     * 成绩表头
     * 类装载时就载入指定好的列头信息，如有需要，可以考虑做成动态生成的列头
     *
     * @return 列表头集合
     */
    private static List<String> addScoreList() {
        List<String> excelList = new ArrayList<>();
        excelList.add("姓名");
        excelList.add("学号");
        excelList.add("院系");
        excelList.add("专业班级");
        excelList.add("课程代号");
        excelList.add("课程名");
        excelList.add("课程类别");
        excelList.add("学分");
        excelList.add("成绩");
        excelList.add("教师");
        return excelList;
    }

    /**
     * 生成Excel并写入数据信息
     *
     * @param dataList 数据列表
     * @return 写入数据后的工作簿对象
     */
    private Workbook exportData(List<T> dataList, Object object) {
        // 创建xlsx的Excel
        Workbook workbook = new SXSSFWorkbook();
        // 生成Sheet表，写入第一行的列头
        Sheet sheet = buildDataSheet(workbook);
        //构建每一行数据
        int rowNum = 1;
        for (T dataVo : dataList) {
            if (dataVo == null) {
                continue;
            }
            //输出行数据
            if (sheet != null) {
                Row row = sheet.createRow(rowNum++);
                //判断类型，调用对应函数
                if (object instanceof Basicinformation) {
                    convertDataToRowStu((Basicinformation) dataVo, row, sheet);
                } else if (object instanceof Achievement) {
                    convertDataToRowScore((Achievement) dataVo, row, sheet);
                }
            }
        }
        return workbook;
    }

    /**
     * 学生基本信息将数据转换成行
     *
     * @param basicinformation 源数据
     * @param row              行对象
     */
    private void convertDataToRowStu(Basicinformation basicinformation, Row row, Sheet sheet) {
        int cellNum = 0;
        Cell cell;
        CellStyle style = buildCellStyle(sheet.getWorkbook());
        // 学号
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getBasicinformationId() ? "" : basicinformation.getBasicinformationId());
        cell.setCellStyle(style);
        // 姓名
        cell = row.createCell(cellNum++);
        if (basicinformation.getBasicinformationName() != null) {
            cell.setCellValue(basicinformation.getBasicinformationName());
        } else {
            cell.setCellValue("");
        }
        cell.setCellStyle(style);
        // 院系
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getInstitute().getInstituteName() ? "" : basicinformation.getInstitute().getInstituteName());
        cell.setCellStyle(style);
        // 专业班级
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getBasicinformationClass() ? "" : basicinformation.getBasicinformationClass());
        cell.setCellStyle(style);
        // 身份证号
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getBasicinformationIcard() ? "" : basicinformation.getBasicinformationIcard());
        cell.setCellStyle(style);
        // 家庭住址
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getBasicinformationHome() ? "" : basicinformation.getBasicinformationHome());
        cell.setCellStyle(style);
        // 获取性别
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getBasicinformationSex() ? "" : basicinformation.getBasicinformationSex());
        cell.setCellStyle(style);
        // 民族
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getBasicinformationNation() ? "" : basicinformation.getBasicinformationNation());
        cell.setCellStyle(style);
        // 联系电话
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getBasicinformationPhone() ? "" : basicinformation.getBasicinformationPhone());
        cell.setCellStyle(style);
        // 出生地
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getBasicinformationBirthplace() ? "" : basicinformation.getBasicinformationBirthplace());
        cell.setCellStyle(style);
        // 籍贯
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getBasicinformationNativeplace() ? "" : basicinformation.getBasicinformationNativeplace());
        cell.setCellStyle(style);
        // 生源地
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getBasicinformationSorigin() ? "" : basicinformation.getBasicinformationSorigin());
        cell.setCellStyle(style);
        // 考生号
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getBasicinformationCandidatenumber() ? "" : basicinformation.getBasicinformationCandidatenumber());
        cell.setCellStyle(style);
        // 角色
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getBasicinformationRole() ? "" : basicinformation.getBasicinformationRole());
        cell.setCellStyle(style);
        // 密码
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getBasicinformationPassword() ? "" : basicinformation.getBasicinformationPassword());
        cell.setCellStyle(style);
        // 贷款金额
        cell = row.createCell(cellNum++);
        if (basicinformation.getLoanAmount() != null) {
            cell.setCellValue(basicinformation.getLoanAmount());
        } else {
            cell.setCellValue("");
        }
        cell.setCellStyle(style);
        // 贷款回执单
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getLoanNumber() ? "" : basicinformation.getLoanNumber());
        cell.setCellStyle(style);
        // 贷款类型
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == basicinformation.getLoanType() ? "" : basicinformation.getLoanType());
        cell.setCellStyle(style);
    }

    /**
     * 学生成绩表将数据转换成行
     *
     * @param achievement 源数据
     * @param row         行对象
     */
    private void convertDataToRowScore(Achievement achievement, Row row, Sheet sheet) {
        int cellNum = 0;
        Cell cell;
        CellStyle style = buildCellStyle(sheet.getWorkbook());

        // 姓名
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == achievement.getBasicinformation().getBasicinformationName() ? "" : achievement.getBasicinformation().getBasicinformationName());
        cell.setCellStyle(style);
        // 学号
        cell = row.createCell(cellNum++);
        if (achievement.getBasicinformation().getBasicinformationId() != null) {
            cell.setCellValue(achievement.getBasicinformation().getBasicinformationId());
        } else {
            cell.setCellValue("");
        }
        cell.setCellStyle(style);
        // 院系
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == achievement.getInstitute().getInstituteName() ? "" : achievement.getInstitute().getInstituteName());
        cell.setCellStyle(style);
        // 专业班级
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == achievement.getBasicinformation().getBasicinformationClass() ? "" : achievement.getBasicinformation().getBasicinformationClass());
        cell.setCellStyle(style);
        // 课程代号
        cell = row.createCell(cellNum++);
        if (achievement.getCourse().getCourseCoursenume() != null) {
            cell.setCellValue(achievement.getCourse().getCourseCoursenume());
        } else {
            cell.setCellValue("");
        }
        cell.setCellStyle(style);
        // 课程名
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == achievement.getCourse().getCourseCoursename() ? "" : achievement.getCourse().getCourseCoursename());
        cell.setCellStyle(style);
        // 课程类别
        cell = row.createCell(cellNum++);
        if (achievement.getCourse().getCourseCategory() != null) {
            if (achievement.getCourse().getCourseCategory() == 1) {
                cell.setCellValue("必修");
            } else if (achievement.getCourse().getCourseCategory() == 0) {
                cell.setCellValue("选修");
            }
        } else {
            cell.setCellValue("");
        }
        cell.setCellStyle(style);
        // 学分
        cell = row.createCell(cellNum++);
        if (achievement.getCourse().getCourseCredit() != null) {
            cell.setCellValue(achievement.getCourse().getCourseCredit());
        } else {
            cell.setCellValue("");
        }
        cell.setCellStyle(style);
        // 成绩
        cell = row.createCell(cellNum++);
        if (achievement.getAchievementGrade() != null) {
            cell.setCellValue(achievement.getAchievementGrade());
        } else {
            cell.setCellValue("");
        }
        cell.setCellStyle(style);
        // 教师
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == achievement.getTeacher().getTeacherName() ? "" : achievement.getTeacher().getTeacherName());
        cell.setCellStyle(style);
    }

    /**
     * 导出工作簿
     *
     * @param dataVOList 数据集合
     * @param excelName  导出文件名
     * @param response
     */
    public void export(List<T> dataVOList, String excelName, HttpServletResponse response, Object object) {

        Workbook workbook = null;
        OutputStream out = null;
        try {

            //判断类型调用方法
            if (object instanceof Basicinformation) {
                CELL_HEADS = addStuList();
            } else if (object instanceof Achievement) {
                CELL_HEADS = addScoreList();
            }

            //工作簿创建成功
            workbook = exportData(dataVOList, object);
            /*String excelName = "示例Excel导出";*/
            String fileName = excelName + ".xlsx";
            fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setContentType("application/x-download");
            response.setCharacterEncoding("UTF-8");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            response.flushBuffer();
            out = response.getOutputStream();
            workbook.write(out);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
                if (workbook != null) {
                    workbook.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

}

